﻿using Dapper;
using Domain.Entity;
using Domain.Entity.bs;
using Domain.Entity.In;
using Domain.Entity.search;
using Domain.IRepository;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class StockRepository : IStockRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";

        //添加库存
        public async Task<int> AddStock(Stock stock)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"insert into [dbo].[bs_stock](proc_id, wh_id, slot_id, stk_code, stk_hand_num, stk_total, stk_price, remark)
                                values({stock.proc_id},{stock.wh_id},{stock.slot_id},'{stock.stk_code}',{stock.stk_hand_num},'{stock.stk_total}','{stock.stk_price}','{stock.remark}')";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        // 库存查询
        public async Task<FenYe<StockInventoryViewDto>> GetStockAsync(FenYe<StockInventoryViewDto> fenYe, string? name, string? unit, int? whid, string? type, bool showStopped, bool filterZeroStock)
        {
            await using var connection = new SqlConnection(con);
            await connection.OpenAsync();

            var sql = new StringBuilder(@"
        SELECT 
            p.proc_code,
            p.proc_name,
            p.proc_status,
            p.type_name,
            p.proc_model,
            p.proc_primary_unit,
            p.proc_primary_barcode,
            w.wh_name,
            t.slot_name,
            COALESCE(s.stk_hand_num, 0) AS proc_inventory,
            COALESCE(s.stk_hand_num, 0) AS proc_ky_inventory,
            COALESCE(s.stk_total, 0) AS proc_inventory_money,
            COALESCE(s.stk_total, 0) AS proc_ky_inventory_money,
            w.wh_id,
            p.create_time
        FROM bs_product p
        LEFT JOIN bs_stock s ON p.proc_id = s.proc_id
        LEFT JOIN bs_warehouse w ON w.wh_id = s.wh_id AND w.is_delete = 0
        LEFT JOIN bs_slot t ON t.slot_id = s.slot_id AND t.is_delete = 0
        WHERE p.is_delete = 0");

            var parameters = new DynamicParameters();

            // 添加筛选条件
            if (!string.IsNullOrEmpty(name))
            {
                sql.Append(" AND (p.proc_code LIKE @Name OR p.proc_name LIKE @Name)");
                parameters.Add("Name", $"%{name}%");
            }

            if (!string.IsNullOrEmpty(unit))
            {
                sql.Append(" AND p.proc_primary_unit = @Unit");
                parameters.Add("Unit", unit);
            }

            if (whid.HasValue)
            {
                sql.Append(" AND w.wh_id = @Whid");
                parameters.Add("Whid", whid.Value);
            }

            if (!string.IsNullOrEmpty(type))
            {
                sql.Append(" AND p.type_id = @Type");
                parameters.Add("Type", type);
            }

            if (!showStopped)
            {
                sql.Append(" AND proc_status=0");
            }

            if (filterZeroStock)
            {
                sql.Append(" AND proc_inventory!=0");
            }

            // 查询总数
            var countSql = $"SELECT COUNT(1) FROM ({sql}) AS temp";
            var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);

            // 分页查询
            var pagedSql = $@"
    WITH StockData AS ({sql})
    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER (ORDER BY wh_name, slot_name, proc_code) AS RowNum
        FROM StockData
    ) AS PagedData
    WHERE RowNum BETWEEN {(fenYe.CurrentPage - 1) * fenYe.PageSize + 1} 
                     AND {fenYe.CurrentPage * fenYe.PageSize}
    ORDER BY wh_name, slot_name, proc_code";

            var stockItems = (await connection.QueryAsync<StockInventoryViewDto>(pagedSql, parameters)).ToList();
            fenYe.List = stockItems;
            fenYe.Rows = totalCount;
            return fenYe;
        }

        public async Task<StockTotals> Gettotal()
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                string sql = @"
                SELECT 
                    SUM(out_num) AS out_num,
                    SUM(in_num) AS in_num,
                    SUM(out_outstockNO_num) AS out_outstockNO_num,
                    SUM(in_instockNO_num) AS in_instockNO_num
                FROM 
                    (SELECT 
                        SUM(CASE WHEN is_delete = 0 THEN out_num ELSE 0 END) AS out_num,
                        SUM(CASE WHEN is_delete = 0 THEN out_outstockNO_num ELSE 0 END) AS out_outstockNO_num,
                        0 AS in_num,
                        0 AS in_instockNO_num
                    FROM out_outstock_h WHERE is_delete = 0
    
                    UNION ALL
    
                    SELECT 
                        0 AS out_num,
                        0 AS out_outstockNO_num,
                        SUM(CASE WHEN is_delete = 0 THEN in_num ELSE 0 END) AS in_num,
                        SUM(CASE WHEN is_delete = 0 THEN in_instockNO_num ELSE 0 END) AS in_instockNO_num
                    FROM in_instock_h WHERE is_delete = 0
                    ) t;";

                string finalSql = sql;

                using (SqlCommand command = new SqlCommand(finalSql, sqlConnection))
                {
                    using (SqlDataReader reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            return new StockTotals
                            {
                                out_num = reader.GetInt32(0),
                                in_num = reader.GetInt32(1),
                                out_outstockNO_num = reader.GetInt32(2),
                                in_instockNO_num = reader.GetInt32(3),
                            };
                        }
                        return new StockTotals();
                    }
                }
            }
        }

        //查询是否已存在相同商品、仓库、库位的记录
        public async Task<Stock> GetStockByCriteria(int procid, int whid, int slotid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"select * from [dbo].[bs_stock] where proc_id={procid} and wh_id={whid} and slot_id={slotid}";
                return await sqlConnection.QueryFirstOrDefaultAsync<Stock>(sql);
            }
        }

        //修改库存  
        public async Task<int> UpdateStock(Stock stock)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[bs_stock] set stk_hand_num+={stock.stk_hand_num},stk_total+={stock.stk_total},stk_price={stock.stk_price},remark='{stock.remark}' where proc_id={stock.proc_id} and wh_id={stock.wh_id} and slot_id={stock.slot_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //修改库存(出库)
        public async Task<int> UpdateStockJian(Stock stock)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[bs_stock] set stk_hand_num-={stock.stk_hand_num},stk_total-={stock.stk_total},stk_price={stock.stk_price},remark='{stock.remark}' where proc_id={stock.proc_id} and wh_id={stock.wh_id} and slot_id={stock.slot_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //通过仓库ID查
        public async Task<List<Stock>> GetStockwhid(int whid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"select * from [dbo].[bs_stock] where wh_id={whid}";
                return sqlConnection.Query<Stock>(sql).ToList();
            }
        }


        //库存分布
        public async Task<StockTJResult> StockDto_hTJ(string? timeType, int? page, int? pagesize)
        {
            using (var sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var where = " 1=1 "; // 基础条件
                var parameters = new DynamicParameters();

                // 时间类型条件（如果需要按库存变动时间筛选）
                if (!string.IsNullOrEmpty(timeType))
                {
                    switch (timeType)
                    {
                        case "today":
                            where += " AND CONVERT(date, h.out_date) = CONVERT(date, GETDATE())";
                            break;
                        case "yesterday":
                            where += " AND CONVERT(date, h.out_date) = CONVERT(date, DATEADD(day, -1, GETDATE()))";
                            break;
                        case "week":
                            where += " AND DATEPART(week, h.out_date) = DATEPART(week, GETDATE()) AND YEAR(h.out_date) = YEAR(GETDATE())";
                            break;
                        case "lastWeek":
                            where += " AND DATEPART(week, h.out_date) = DATEPART(week, DATEADD(week, -1, GETDATE()))"
                                  + " AND YEAR(h.out_date) = YEAR(DATEADD(week, -1, GETDATE()))";
                            break;
                        case "month":
                            where += " AND MONTH(h.out_date) = MONTH(GETDATE()) AND YEAR(h.out_date) = YEAR(GETDATE())";
                            break;
                        case "lastMonth":
                            where += " AND MONTH(h.out_date) = MONTH(DATEADD(month, -1, GETDATE()))"
                                  + " AND YEAR(h.out_date) = YEAR(DATEADD(month, -1, GETDATE()))";
                            break;
                    }
                }

                // 1. 查询总数（改为统计记录数而非distinct wh_id）
                string countSql = $@"
            SELECT COUNT(1) 
            FROM [dbo].[bs_stock] h
            WHERE {where}";

                int total = await sqlConnection.ExecuteScalarAsync<int>(countSql, parameters);

                // 2. 查询统计信息（保持不变）
                string statSql = $@"
            SELECT 
                ISNULL(SUM(stk_hand_num), 0) AS total_stk_hand_num,
                ISNULL(SUM(stk_total), 0) AS total_stk_total
            FROM [dbo].[bs_stock] h
            WHERE {where}";

                var stat = await sqlConnection.QueryFirstOrDefaultAsync<dynamic>(statSql, parameters);

                // 3. 查询库存数据（改为直接查询，不分组）
                string sql = $@"
            SELECT 
                wh_id,
                stk_hand_num,
                stk_total
            FROM [dbo].[bs_stock] h
            WHERE {where}
            ORDER BY wh_id
            OFFSET @PageSize * (@PageIndex - 1) ROWS
            FETCH NEXT @PageSize ROWS ONLY";

                parameters.Add("PageIndex", page);
                parameters.Add("PageSize", pagesize);

                var list = (await sqlConnection.QueryAsync<StockDto>(sql, parameters)).ToList();

                return new StockTJResult
                {
                    list = list,
                    total = total,
                    total_stk_hand_num = stat?.total_stk_hand_num ?? 0,
                    total_stk_total = stat?.total_stk_total ?? 0
                };
            }
        }

        public async Task<List<StockSearch>> GetByPid(int procid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select w.wh_name,l.slot_name,stk_hand_num,stk_total,p.proc_max,p.proc_min from [dbo].[bs_stock] s 
                                inner join [dbo].[bs_product] p on p.proc_id=s.proc_id
                                inner join [dbo].[bs_warehouse] w on w.wh_id=s.wh_id
                                inner join [dbo].[bs_slot] l on l.slot_id=s.slot_id
                                where s.proc_id={procid}";
                return sqlConnection.Query<StockSearch>(sql).ToList();
            }
        }
    }
}
